﻿#region Header
// ------------------------ Licence / Copyright ------------------------
// 
// ScrumTable for Scrum Meeting Support
// Copyright © HSR - Hochschule für Technik Rapperswil
// All Rights Reserved
// 
// Author:
//  Michael Gfeller Silvan Gehrig Patrick Boos
// 
// ---------------------------------------------------------------------
#endregion

#region Usings

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using ScrumTable.Common;
using ScrumTable.Common.Collections;
using ScrumTable.Common.Data;
using ScrumTable.Common.Excel.OpenXmlSdk;
using ScrumTable.Common.Excel.OpenXmlSdk.Extensions;
using ScrumTable.Common.Net;
using ScrumTable.Common.Security;
using ScrumTable.DL.Data.Excel.Services.Excel;
using ScrumTable.DL.Data.Generic.Cache;
using ScrumTable.DL.Data.Schema;
using ScrumTable.DL.Data.Schema.ScrumTableV1;

#endregion

namespace ScrumTable.DL.Data.Excel
{
    /// <summary>
    /// Provides the facilities for deserialization of an xml file.
    /// </summary>
    internal class DataProvider
    {
        #region Declarations
        //--------------------------------------------------------------------
        // Declarations
        //--------------------------------------------------------------------

        private readonly CacheContext _context;
        private readonly ScrumTableV1Schema _schema;

        private bool _dataLoaded = false;

        // key: member back-end id (display name / name id from excel)
        // value: member
        private readonly IDictionary<string, CacheMember> _members = new Dictionary<string, CacheMember>();

        // key: iteration back-end id (name id from excel)
        // value: iteration
        private readonly IDictionary<string, CacheIteration> _iterations = new Dictionary<string, CacheIteration>();

        // key: parent element id of attachment
        // value: attachment
        private readonly IDictionary<string, IList<CacheAttachment>> _attachments =
            new Dictionary<string, IList<CacheAttachment>>();

        // key: workitem back-end id (schema mapped name)
        // value: CacheWorkItemType
        private readonly IDictionary<string, CacheWorkItemType> _workItemTypes =
            new Dictionary<string, CacheWorkItemType>();

        // keys: int -> id from excel; string -> name id from excel
        // value: CacheWorkItemType
        private readonly WorkItemMultiIndex _workItems = new WorkItemMultiIndex();

        #endregion

        #region Properties

        //--------------------------------------------------------------------
        // Properties
        //--------------------------------------------------------------------

        /// <summary>
        /// Gets the loaded project instance.
        /// </summary>
        internal IEnumerable<CacheProject> Projects
        {
            get
            {
                if (Project != null)
                {
                    return new Enumerable<CacheProject>(Project);
                }
                return new Enumerable<CacheProject>(new CacheProject[0]);
            }
        }

        /// <summary>
        /// Gets the loaded project instance.
        /// </summary>
        private CacheProject Project { get; set; }

        private ConnectionInformationManager ConnectionContext { get; set; }
        private WorkbookMapper ExcelWorkbookMapper { get; set; }
        private string ExcelFilePath { get; set; }

        #endregion

        #region Constructors / Destructor

        //--------------------------------------------------------------------
        // Constructors / Destructor
        //--------------------------------------------------------------------

        /// <summary>
        /// Creates a new DataProvider instance.
        /// </summary>
        /// <param name="context">Specifies the data context which contains the methods to create the child elements.</param>
        /// <param name="connectionCtx">Specifies the connection information context.</param>
        internal DataProvider(CacheContext context, ConnectionInformationManager connectionCtx)
        {
            PreCondition.AssertNotNull(context, "context");
            PreCondition.AssertNotNull(connectionCtx, "connectionCtx");

            ConnectionContext = connectionCtx;

            _context = context;
            _schema = context.Schema;
        }

        #endregion

        #region Methods
        //--------------------------------------------------------------------
        // Methods
        //--------------------------------------------------------------------

        #region Load Project Data

        /// <summary>
        /// Loads the project data blow the given project key chain.
        /// </summary>
        /// <param name="idChain">The data below the given id should be imported.</param>
        internal void LoadProjectData(ValueChain<string> idChain)
        {
            PreCondition.AssertNotNull(idChain, "idChain");

            if (!_dataLoaded)
            {
                LoadIterationData();
                LoadWorkItemData();

                Project.ImportData(
                    _iterations.Values.ToList(),
                    null /* reports aren't supported by the excel data driver */,
                    _workItems.Convert(idxEntry => new KeyValuePair<int, CacheWorkItem>(idxEntry.Id, idxEntry.WorkItem)));

                if (Project.UniqueId == idChain.Bottom)
                {
                    _context.CurrentProject = Project;
                }
            }
            _dataLoaded = true;
        }

        private void LoadIterationData()
        {
            if (ExcelWorkbookMapper.IterationsSheet.IsContained
                && ExcelWorkbookMapper.IterationsSheet.Sheet.HasPrimaryKey())
            {
                double orderIdx = 0;

                foreach (DataRow contentRow in ExcelWorkbookMapper.IterationsSheet.Sheet.Rows)
                {
                    string name = contentRow.GetColumnValue(_schema.IterationSchema.Name.SchemaMappedName, string.Empty);
                    if (string.IsNullOrEmpty(name)) { continue; }

                    DateTime current = DateTime.Now;
                    DateTime startTime = contentRow.GetColumnValue(_schema.IterationSchema.StartTime.SchemaMappedName, current);
                    DateTime endTime = contentRow.GetColumnValue(_schema.IterationSchema.EndTime.SchemaMappedName, current);

                    int interationId = contentRow.TryGetFirstPrimaryKey(RefIdConverter.DefaultId);

                    if (interationId != RefIdConverter.DefaultId)
                    {
                        string backEndId = RefIdConverter.ToRefId(interationId, name);

                        _iterations[backEndId] = _context.ElementFactory.CreateIteration(
                            backEndId,
                            interationId,
                            orderIdx++,
                            name,
                            name,
                            startTime,
                            endTime);
                    }
                }
            }
        }

        private void LoadWorkItemData()
        {
            IndexWorkItems();
            EstablishWorkItemLinks();
            CreateAttachments();
            CreateWorkItems();
        }

        private void IndexWorkItems()
        {
            IndexWorkItemsByType(ExcelWorkbookMapper.UserStorySheet, _workItemTypes[_schema.UserStorySchema.SchemaMappedName]);
            IndexWorkItemsByType(ExcelWorkbookMapper.TaskSheet, _workItemTypes[_schema.TaskSchema.SchemaMappedName]);
            IndexWorkItemsByType(ExcelWorkbookMapper.BugSheet, _workItemTypes[_schema.BugSchema.SchemaMappedName]);
        }

        private void IndexWorkItemsByType(SheetMapping sheetMapping, CacheWorkItemType type)
        {
            if (sheetMapping.IsContained)
            {
                foreach (DataRow contentRow in sheetMapping.Sheet.Rows)
                {
                    string name = contentRow.GetColumnValue(type.NameField.Schema.SchemaMappedName, string.Empty);
                    if (string.IsNullOrEmpty(name)) { continue; }

                    int workItemId = contentRow.TryGetFirstPrimaryKey(RefIdConverter.DefaultId);
                    string backEndId = RefIdConverter.ToRefId(workItemId, name);

                    if (workItemId != RefIdConverter.DefaultId)
                    {
                        _workItems.Set(new WorkItemMultiIndexEntry
                                           {
                                               Data = contentRow,
                                               Id = workItemId,
                                               Type = type,
                                               BackEndId = backEndId
                                           });
                    }
                }
            }
        }

        private void EstablishWorkItemLinks()
        {
            foreach (WorkItemMultiIndexEntry indexEntry in _workItems)
            {
                string parentIdFieldName = ExcelWorkbookMapper.GetParentIdFieldName(indexEntry.Type.Schema);

                if (parentIdFieldName != null)
                {
                    WorkItemMultiIndexEntry parent = GetWorkItemIndexEntryByRefId(indexEntry.Data.GetColumnValue<string>(parentIdFieldName));

                    if (parent != null)
                    {
                        // establish relations with Child/Parent links
                        indexEntry.ParentLinks.Add(_context.ElementFactory.CreateWorkItemLink(parent.Id, parent.BackEndId));
                        parent.ChildLinks.Add(_context.ElementFactory.CreateWorkItemLink(indexEntry.Id, indexEntry.BackEndId));
                    }
                }
            }
        }

        private WorkItemMultiIndexEntry GetWorkItemIndexEntryByRefId(string refId)
        {
            if (!string.IsNullOrEmpty(refId))
            {
                if (_workItems.ContainsBackEndId(refId))
                {
                    return _workItems.GetByBackEndId(refId);
                }

                int workItemId = RefIdConverter.FromRefId(refId);

                if (_workItems.ContainsId(workItemId))
                {
                    return _workItems.GetById(workItemId);
                }
            }
            return null;
        }

        private void CreateAttachments()
        {
            if (ExcelWorkbookMapper.AttachmentsSheet.IsContained
                && ExcelWorkbookMapper.AttachmentsSheet.Sheet.HasPrimaryKey())
            {
                foreach (DataRow contentRow in ExcelWorkbookMapper.AttachmentsSheet.Sheet.Rows)
                {
                    string name = contentRow.GetColumnValue(_schema.AttachmentSchema.Name.SchemaMappedName, string.Empty);
                    if (string.IsNullOrEmpty(name)) { continue; }

                    string uriRaw = contentRow.GetColumnValue(_schema.AttachmentSchema.Uri.SchemaMappedName, string.Empty);
                    Uri attachmentUri = GetLinkedFileUri(uriRaw);
                    int attaId = contentRow.TryGetFirstPrimaryKey(RefIdConverter.DefaultId);

                    if (attaId == RefIdConverter.DefaultId) { continue; }

                    string parentId = GetAttachmentParent(contentRow);

                    if (parentId != null)
                    {
                        CacheAttachment attaToLoad = _context.ElementFactory.CreateAttachment(
                            name,
                            attaId,
                            string.Empty,
                            attachmentUri);

                        GetAttachmentList(parentId).Add(attaToLoad);
                    }
                }
            }
        }

        private Uri GetLinkedFileUri(string uriRaw)
        {
            if (string.IsNullOrWhiteSpace(uriRaw)) { return null; }

            Uri linkedFileUri;

            if (Uri.TryCreate(uriRaw, UriKind.RelativeOrAbsolute, out linkedFileUri))
            {
                // first try: the Uri is absolute or matches a file
                if (File.Exists(linkedFileUri.ToLocalPath()))
                {
                    return linkedFileUri;
                }

                // second try: the Uri is relative to the Excel file
                string relativeExcelPath = Path.Combine(Path.GetDirectoryName(ExcelFilePath), linkedFileUri.ToLocalPath());

                if (File.Exists(relativeExcelPath))
                {
                    return new Uri(relativeExcelPath, UriKind.RelativeOrAbsolute);
                }
            }
            return linkedFileUri;
        }

        private string GetAttachmentParent(DataRow contentRow)
        {
            string parentIdFieldName = ExcelWorkbookMapper.GetParentIdFieldName(_schema.AttachmentSchema);

            if (parentIdFieldName != null)
            {
                string parentId = contentRow.GetColumnValue<string>(parentIdFieldName);
                WorkItemMultiIndexEntry workItem = GetWorkItemIndexEntryByRefId(parentId);

                if (workItem != null)
                {
                    return workItem.BackEndId;
                }
            }
            return null;
        }

        private IList<CacheAttachment> GetAttachmentList(string parentId)
        {
            if (!_attachments.ContainsKey(parentId))
            {
                _attachments[parentId] = new List<CacheAttachment>();
            }
            return _attachments[parentId];
        }

        private void CreateWorkItems()
        {
            foreach (WorkItemMultiIndexEntry indexEntry in _workItems)
            {
                indexEntry.WorkItem = _context.ElementFactory.CreateWorkItem(
                    indexEntry.Type,
                    indexEntry.Id,
                    GetAssignedValue(indexEntry, indexEntry.Type.NameField, string.Empty) as string,
                    0,
                    GetAssignedIteration(indexEntry),
                    GetAssignedState(indexEntry),
                    GetAssignedValue(indexEntry, indexEntry.Type.DescriptionField, string.Empty) as string,
                    GetAssignedMember(indexEntry),
                    GetAssignedValues(indexEntry),
                    indexEntry.ParentLinks,
                    indexEntry.ChildLinks,
                    GetAssignedAttachments(indexEntry),
                    new CacheWorkItemRevision[0]);
            }
        }

        private int GetAssignedIteration(WorkItemMultiIndexEntry indexEntry)
        {
            int itertionId = CacheIdentity.DefaultId;
            string iterationIdFieldName = ExcelWorkbookMapper.GetIterationIdFieldName(indexEntry.Type.Schema);

            if (iterationIdFieldName != null)
            {
                string iterationRefId = indexEntry.Data.GetColumnValue<string>(iterationIdFieldName);

                // first try: directly by RefId -> to Iteration index
                itertionId = GetAssignedIterationByRefId(iterationRefId);

                // if RefId of the first try couldn't be resolved to an iteration id
                if (itertionId == CacheIdentity.DefaultId)
                {
                    // second try: get original Row by the id number inside the RefId
                    DataRow referencedRow;

                    if (ExcelWorkbookMapper.IterationsSheet.TryGetByRefId(iterationRefId, out referencedRow))
                    {
                        // iteration id has been found, let's rebuild a valid reference
                        iterationRefId = RefIdConverter.ToRefId(
                            referencedRow.TryGetFirstPrimaryKey(RefIdConverter.DefaultId),
                            referencedRow.GetColumnValue<string>(_schema.IterationSchema.Name.SchemaMappedName, string.Empty));

                        // if this resolve procedure fails, a CacheIdentity.DefaultId will be returned
                        itertionId = GetAssignedIterationByRefId(iterationRefId);
                    }
                }
            }
            return itertionId;
        }

        private int GetAssignedIterationByRefId(string iterationRefId)
        {
            if (!string.IsNullOrEmpty(iterationRefId))
            {
                if (_iterations.ContainsKey(iterationRefId))
                {
                    return _iterations[iterationRefId].Id;
                }
            }
            return CacheIdentity.DefaultId;
        }

        private Dictionary<string, object> GetAssignedValues(WorkItemMultiIndexEntry indexEntry)
        {
            Dictionary<string, object> values = new Dictionary<string, object>();

            foreach (CacheFieldDefinition fieldDefinition in indexEntry.Type.NonPredefinedFieldDefinitions)
            {
                if (indexEntry.Data.Table.Columns.Contains(fieldDefinition.Schema.SchemaMappedName))
                {
                    values[fieldDefinition.Schema.SchemaMappedName] = GetAssignedValue(indexEntry, fieldDefinition);
                }
            }
            return values;
        }

        private IList<CacheAttachment> GetAssignedAttachments(WorkItemMultiIndexEntry indexEntry)
        {
            if (_attachments.ContainsKey(indexEntry.BackEndId))
            {
                return _attachments[indexEntry.BackEndId];
            }
            return new CacheAttachment[0];
        }

        private object GetAssignedValue(WorkItemMultiIndexEntry indexEntry, CacheFieldDefinition fieldDef, object defaultValue = null)
        {
            object retrievedValue = defaultValue;

            if (fieldDef.HasAllowedValues) // if the field definition has restrictions (allowed values / default values / ...)
            {
                DataValue columnValue = indexEntry.Data.GetColumnValue(fieldDef.Schema.SchemaMappedName);
                object typedValueRaw = columnValue.Value;
                    
                if (SheetValueDefinition.IsDefaultValue(columnValue.ToString()))
                {
                    SheetValueDefinition.GetValueFromDefaultValue(columnValue.ToString()).TryChangeType(fieldDef.Schema.SchemaType, out typedValueRaw);
                }

                if (fieldDef.AllowedValues.Contains(typedValueRaw))
                {
                    retrievedValue = typedValueRaw;
                }
                else
                {
                    retrievedValue = fieldDef.DefaultValue;
                }
            }
            else
            {
                object columnValue;

                if (indexEntry.Data.TryGetColumnValue(fieldDef.Schema.SchemaType, fieldDef.Schema.SchemaMappedName, out columnValue))
                {
                    retrievedValue = columnValue;
                }
            }
            return retrievedValue;
        }

        private string GetAssignedState(WorkItemMultiIndexEntry indexEntry)
        {
            return GetAssignedValue(indexEntry, indexEntry.Type.StateField).ToString();
        }

        private string GetAssignedMember(WorkItemMultiIndexEntry indexEntry)
        {
            string memberRefId = indexEntry.Data.GetColumnValue<string>(indexEntry.Type.MemberIdField.Schema.SchemaMappedName);

            // first try: directly by RefId -> to Iteration index
            string memberId = GetAssignedMemberByRefId(memberRefId);

            // if RefId of the first try couldn't be resolved to an iteration id
            if (string.IsNullOrEmpty(memberId))
            {
                // second try: get original Row by the id number inside the RefId
                DataRow referencedRow;

                if (ExcelWorkbookMapper.MembersSheet.TryGetByRefId(memberRefId, out referencedRow))
                {
                    // iteration id has been found, let's rebuild a valid reference
                    memberRefId = RefIdConverter.ToRefId(
                        referencedRow.TryGetFirstPrimaryKey(RefIdConverter.DefaultId),
                        referencedRow.GetColumnValue<string>(_schema.MemberSchema.Name.SchemaMappedName, string.Empty));

                    // if this resolve procedure fails, a CacheIdentity.DefaultId will be returned
                    memberId = GetAssignedMemberByRefId(memberRefId);
                }
            }
            return memberId;
        }

        private string GetAssignedMemberByRefId(string memberRefId)
        {
            if (!string.IsNullOrEmpty(memberRefId))
            {
                if (_members.ContainsKey(memberRefId))
                {
                    return memberRefId;
                }
            }
            return string.Empty;
        }

        #endregion


        #region Load All Projects And Members

        /// <summary>
        /// Loads the projects from the given file.
        /// </summary>
        /// <param name="filePathToDeserialize">Specifies the path to the xml file to deserialize.</param>
        internal void LoadProjects(string filePathToDeserialize)
        {
            PreCondition.AssertNotNullOrEmpty(filePathToDeserialize, "filePathToDeserialize");

            ExcelFilePath = filePathToDeserialize;
            ExcelWorkbookMapper = new WorkbookMapper(
                new OpenXmlWorkbookSerializer(ExcelFilePath),
                _schema);
            LoadMembers();
            LoadWorkItemTypes();
            LoadProjects();
        }

        private void LoadProjects()
        {
            if (ExcelWorkbookMapper.ProjectSheet.IsContained)
            {
                Project = _context.ElementFactory.CreateProject(
                    1,
                    ExcelWorkbookMapper.ProjectName,
                    ServiceRegistration.Empty,
                    ServiceRegistration.Empty,
                    null,
                    _members.Values,
                    _workItemTypes.Values);
            }
        }

        private void LoadMembers()
        {
            if (ExcelWorkbookMapper.MembersSheet.IsContained
                && ExcelWorkbookMapper.MembersSheet.Sheet.HasPrimaryKey())
            {
                int orderIdx = 0;

                foreach (DataRow contentRow in ExcelWorkbookMapper.MembersSheet.Sheet.Rows)
                {
                    int memberId = contentRow.TryGetFirstPrimaryKey(RefIdConverter.DefaultId);
                    if (memberId == RefIdConverter.DefaultId) { continue; }

                    string name = contentRow.GetColumnValue(_schema.MemberSchema.Name.SchemaMappedName, string.Empty);
                    if (string.IsNullOrEmpty(name)) { continue; }

                    string eMail = contentRow.GetColumnValue(_schema.MemberSchema.EMail.SchemaMappedName, string.Empty);
                    string displayName = RefIdConverter.ToRefId(memberId, name);
                    string picUri = contentRow.GetColumnValue(_schema.MemberSchema.PictureUri.SchemaMappedName, string.Empty);

                    // get member assigned log-in from connection storage
                    ConnectionInformation connectionInfo =
                        ConnectionContext.Where(ci => ci.OriginalUserName == name).FirstOrDefault();

                    CacheMember member = _context.ElementFactory.CreateMember(
                        orderIdx++,
                        displayName,
                        eMail,
                        name,
                        string.Empty,
                        name,
                        (connectionInfo != null),
                        null);

                    // reference the login user to the member
                    if (connectionInfo != null)
                    {
                        connectionInfo.BackEndId = name;
                    }

                    Uri memberUri = GetLinkedFileUri(picUri);

                    if (memberUri != null)
                    {
                        member.PictureUrl = memberUri;
                    }
                    _members[displayName] = member;
                }
            }
        }

        private void LoadWorkItemTypes()
        {
            LoadWorkItemType(_schema.UserStorySchema, _schema.UserStorySchema.State, ExcelWorkbookMapper.UserStoryTypeSettings);
            LoadWorkItemType(_schema.BugSchema, _schema.BugSchema.State, ExcelWorkbookMapper.BugTypeSettings);
            LoadWorkItemType(_schema.TaskSchema, _schema.TaskSchema.State, ExcelWorkbookMapper.TaskTypeSettings);
        }

        private void LoadWorkItemType(SchemaBase workItemTypeSchema, SchemaBase stateSchema, DataRow toLoadTypeFrom)
        {
            SheetValueDefinition stateRow =
                (toLoadTypeFrom != null)
                    ? new SheetValueDefinition(toLoadTypeFrom)
                    : null;

            CacheWorkItemType newType = _context.ElementFactory.CreateWorkItemType(
                workItemTypeSchema.SchemaMappedName,
                workItemTypeSchema,
                GetFieldDefinitions(workItemTypeSchema, stateSchema, stateRow),
                _context.ElementFactory.CreateWorkflow(
                    GetTransitions(stateSchema, stateRow),
                    stateSchema,
                    true));

            _workItemTypes[workItemTypeSchema.SchemaMappedName] = newType;
        }

        private IEnumerable<CacheTransition> GetTransitions(SchemaBase stateSchema, SheetValueDefinition stateRow)
        {
            return _context.ElementFactory.CreateTransitions(
                (stateRow != null)
                    ? stateRow.Values
                    : stateSchema.SchemaDefaultAllowedValues);
        }

        private IEnumerable<CacheFieldDefinition> GetFieldDefinitions(SchemaBase workItemTypeSchema, SchemaBase stateSchema, SheetValueDefinition stateRow)
        {
            IList<CacheFieldDefinition> fieldDefinitions = new List<CacheFieldDefinition>();

            // init state values (according to workflow)
            fieldDefinitions.Add(_context.ElementFactory.CreateFieldDefinition(
                        stateSchema.SchemaDisplayName,
                        stateSchema,
                        DataType.PlainText,
                        (stateRow != null && stateRow.Values.Count > 0) ? stateRow.Values : stateSchema.SchemaDefaultAllowedValues,
                        (stateRow != null && stateRow.DefaultValue != null) ? stateRow.DefaultValue : stateSchema.SchemaDefaultValue));

            foreach (SchemaBase childSchema in workItemTypeSchema.Schemata)
            {
                if (childSchema == stateSchema) { continue; }

                DataRow fieldDefinitionRow = ExcelWorkbookMapper.SettingsSheet.IsContained
                                             ? ExcelWorkbookMapper.SettingsSheet.Sheet.SearchForRow(childSchema.SchemaMappedName)
                                             : null;
                SheetValueDefinition rowValueDefinition = (fieldDefinitionRow != null)
                    ? new SheetValueDefinition(fieldDefinitionRow, childSchema.SchemaType)
                    : new SheetValueDefinition(childSchema.SchemaType);

                fieldDefinitions.Add(_context.ElementFactory.CreateFieldDefinition(
                    childSchema.SchemaDisplayName,
                    childSchema,
                    (!childSchema.IsSchemaReference) ? DataTypeConverter.TryToDataType(childSchema.SchemaType) : DataType.PlainText,
                    rowValueDefinition.Values,
                    rowValueDefinition.DefaultValue));
            }
            return fieldDefinitions;
        }

        #endregion

        #endregion

        #region Events

        //--------------------------------------------------------------------
        // Events
        //--------------------------------------------------------------------

        #endregion

    }
}